'''

INSERT INTO midea_lims_new.equipscadanode
(oid, createTime, editTime, equipmentNumber, equipACode, checkCode, signalType, workLocation, blockName, buId)
VALUES(2000000020960, '2020-08-27 18:23:24', '2020-08-27 18:23:24', 'TA2211080009170021010101', 'A2211080009170021', '00000000000001010102', '断续测试信号', '工位1', 'MITBG', 21);


excel 中的前5列，分别是：equipACode，equipmentNumber，signalType，checkCode，workLocation

读取excel，根据前5列，构造上面的插入语句

'''

import pandas as pd
from datetime import datetime


def generate_sql_from_excel(file_path, start_oid=2000000020960):
    """
    从Excel前5个sheet生成SQL插入语句

    参数:
        file_path: Excel文件路径
        start_oid: 起始oid值(默认2000000021200)
    """
    sql_statements = []
    current_oid = start_oid

    # 循环读取前5个sheet(索引0-4)
    for sheet_index in range(4):
        try:
            df = pd.read_excel(file_path, sheet_name=sheet_index, dtype={'属性编码': str})

            for _, row in df.iterrows():
                if pd.isna(row.get('台位编码')):
                    continue

                current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

                sql = f"INSERT INTO midea_lims_new.equipscadanode" \
                f"(oid, createTime, editTime, equipmentNumber, equipACode, checkCode, signalType, workLocation, blockName, buId)" \
                f"VALUES({current_oid},'{current_time}','{current_time}','{row['台位编码']}','{row['设备A码']}','{str(row['属性编码'])}','{row['*属性名称']}','{row['工位']}','MITBG',21);"

                sql_statements.append(sql)
                current_oid += 1  # oid自增

        except Exception as e:
            print(f"读取sheet{sheet_index+1}时出错: {str(e)}")
            continue

    return sql_statements

# 使用示例
file_path = 'D:\\Users\\ex_yejing1\\Desktop\\压缩机设备联机20250513\\40个设备信号测点配置表_1.xlsx'
sqls = generate_sql_from_excel(file_path)

# 打印结果
print(f"共生成 {len(sqls)} 条SQL语句")
for sql in sqls:
    print(sql)
